﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.OleDb;
using System.Data;
using System.IO;


public partial class _Default : System.Web.UI.Page
{
    ExcelHelper ehelper;
    MsSqlHelper shelper;

    MsSqlDao msdao;
    CSClass CS = new CSClass();
    DataTable dt;

    private static string tempfilename="";

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            Data_Binding();
        }
    }

    private void Data_Binding()
    {
        shelper = new MsSqlHelper();
        shelper.Conn.Open();
        msdao = new MsSqlDao();

        string sql;
        sql = "select  Did as [value],rtrim(DepartmentName) as [key] from Web_Department where beactive='是' ";
        
        dt = msdao.FindAll(shelper.Conn, sql);
        this.DropDownList1.DataSource = dt;
        this.DropDownList1.DataTextField = "key";
        this.DropDownList1.DataValueField = "value";
        this.DropDownList1.DataBind();
        this.DropDownList1.Items.Insert(0, new ListItem("--选择--", "0"));

        sql = "select rqtime as [value],rtrim(rqtime) as [key] from [Web_Laborage] where is_zx='否'"
            + " union "
            + "select "
            +"convert(varchar(7),DATEADD(mm, DATEDIFF(m,0,cast(max(rtrim(rqtime))+'-1' as datetime))+1, 0),120) as [value],"
            +"convert(varchar(7),DATEADD(mm, DATEDIFF(m,0,cast(max(rtrim(rqtime))+'-1' as datetime))+1, 0),120)+'[新建]'  as [key] "
            + " from [Web_Laborage] where isdate(rqtime+'-01')=1 ";        
        dt = msdao.FindAll(shelper.Conn, sql);
        this.DropDownList2.DataSource = dt;
        this.DropDownList2.DataTextField = "key";
        this.DropDownList2.DataValueField = "value";
        this.DropDownList2.DataBind();
        this.DropDownList2.Items.Insert(0, new ListItem("--选择--", "0"));
    }
    
    /// <summary>
    /// 加载Excel数据源
    /// </summary>
    protected void GridDataExcelSource(string Path)
    {
        DataSet ds = new DataSet();
        NPOIHelper Phelper = new NPOIHelper();
        DataTable dt = Phelper.ExcelToTable(Path);
        ds.Tables.Add(dt);
        GridView1.DataSource = ds;
        GridView1.DataBind();
    }
    /// <summary>
    /// 计算考勤
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void Button0_Click(object sender, EventArgs e)
    {
        try
        {
            ListText.Text = null;
            GridView1.DataSource = null;
            GridView1.DataBind();
            string month = DropDownList2.SelectedValue.ToString();
            if (month == "0")
            {
                ListText.Text = "【<font color=red>请选择月份</font>】";
            }
            else
            {
                shelper = new MsSqlHelper();
                int ret = shelper.ExecuteSql("Exec Evaluation_t '" + month + "'");
                if (ret == 1)
                {
                    ListText.Text = "【<font color=green>计算成功</font>】";
                }
                else
                {
                    ListText.Text = "【<font color=red>计算失败</font>】";
                }
            }

        }
        catch (Exception ex)
        {
            CS.WriteLog(ex.ToString(), true);
            ListText.Text = "ERROR :" + ex.Message.ToString();
        }
    }

    /// <summary>
    /// 预览
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void Button2_Click(object sender, EventArgs e)
    {
        try
        {
            ListText.Text = null;
            GridView1.DataSource = null;
            GridView1.DataBind();
            string type = Path.GetExtension(FileUpload1.FileName);
            if (!Directory.Exists(Server.MapPath("/Down/")))
                Directory.CreateDirectory(Server.MapPath("/Down/"));
            if (FileUpload1.FileName == "")
            {
                ListText.Text = "【<font color=red>请选择Excel文件</font>】";
            }
            else
            {
                string filePath = "Down/" + FileUpload1.FileName;
                if (File.Exists(Server.MapPath(filePath)))
                {
                    File.Delete(Server.MapPath(filePath));
                }
                FileUpload1.SaveAs(Server.MapPath(filePath));
                GridDataExcelSource(Server.MapPath(filePath));
                tempfilename = FileUpload1.FileName;
            }
        }
        catch (Exception ex)
        {
            CS.WriteLog(ex.ToString(), true);
            ListText.Text = "ERROR :" + ex.Message + ex.ToString();
        }
    }
    /// <summary>
    /// 导入工资数据
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void Button3_Click(object sender, EventArgs e)
    {
        try
        {
            ListText.Text = null;
            GridView1.DataSource = null;
            GridView1.DataBind();
            //Response.Write("【" + tempfilename + "】<br>");
            if (tempfilename == "")
            {
                ListText.Text = "【<font color=red>请选择Excel文件</font>】";
            }
            else
            {
                if (!Directory.Exists(Server.MapPath("/Down/")))
                    Directory.CreateDirectory(Server.MapPath("/Down/"));
                string filePath = "Down/" + tempfilename;
                int i = 0;
                /*                
                string sql = "select * from [工资数据$]";
                ehelper = new ExcelHelper(Server.MapPath(filePath));
                ehelper.Conn.Open();
                ExcelDao dao = new ExcelDao();
                DataTable dt = dao.FindAll(ehelper.Conn, sql);
                */
                NPOIHelper Phelper = new NPOIHelper();
                DataTable dt = Phelper.ExcelToTable(Server.MapPath(filePath));

                if (dt.Equals(null)) Response.Write("dt = null");

                foreach (DataRow item in dt.Rows)
                {
                    //将Excel中的内容存入缓存中，一条条插入mysql数据库中
                    if (item.IsNull(0)) continue;
                    string dmsql = "delete from [ExcelDao] where ygbh=@p1 and month=@p0";
                    string msql = "insert into [ExcelDao] (month,ygbh,xingming,rsjbqyshj,DepartmentName,idcard,jxf,yygz,qtgz,sdf,qtdk,"
                        + "OverTime,HourTime,OtherJe,sbcdcs,xbztcs,sbwkqcs,xbwkqcs,sjts,bjts,nxjts,wgwj)"
                        +"values"
                        + "(@p0,@p1,@p2,@p3,@p4,@p5,@p6,@p7,@p8,@p9,@p10,@p11,@p12,@p13,@p14,@p15,@p16,@p17,@p18,@p19,@p20,@p21)";
                    shelper = new MsSqlHelper();
                    shelper.Conn.Open();
                    MsSqlDao dao1 = new MsSqlDao();
                    dao1.Insert(shelper.Conn, dmsql, item, i);
                    i = dao1.Insert(shelper.Conn, msql, item, i);
                    shelper.Conn.Close();
                }
                if (i > 1)
                {
                    ListText.Text = "【<font color=green>导入成功</font>】";
                }
                else
                {
                    ListText.Text = "【<font color=red>导入失败</font>】";
                }
                //ehelper.Conn.Close();

                if (File.Exists(Server.MapPath(filePath)))
                {
                    File.Delete(Server.MapPath(filePath));
                }
            }
        }
        catch (Exception ex)
        {
            CS.WriteLog(ex.ToString(), true);
            ListText.Text = "ERROR :" + ex.Message;
        }
    }
    /// <summary>
    /// 导出工资模板
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void Button1_Click(object sender, EventArgs e)
    {
        try
        {
            ListText.Text = null;
            GridView1.DataSource = null;
            GridView1.DataBind();
            int Did = int.Parse(DropDownList1.SelectedValue.ToString());
            string month = DropDownList2.SelectedValue.ToString();
            string DepartmentName = DropDownList1.SelectedItem.Text.ToString();
            if (month == "0")
            {
                ListText.Text = "【<font color=red>请选择月份</font>】";
            }
            else if (Did == 0)
            {
                ListText.Text = "【<font color=red>请选择部门</font>】";
            }
            else
            {
                object[,] title = { { "序号", "月份", "员工编号", "姓名", "入司时间", "部门", "身份证号码", "个人绩效", "提成", "其他工资", "其他应扣", "水电费", "其它代扣", "加班天数", "加班时长", "其它加班", "迟到", "早退", "上班未打卡", "下班未打卡", "事假", "病假", "年假" } };
                string sql = "select rtrim(b.rqtime) as rqtime,isnull(b.ygbh,a.ygbh) ygbh,rtrim(a.xingming) as xingming,rtrim(a.rsjbqyshj) as rsjbqyshj,rtrim(c.DepartmentName) as DepartmentName,rtrim(idcard) as idcard,"
                    + " isnull(jxf,0) as jxf,isnull(yygz,0) as yygz,isnull(qtgz,0) as qtgz,isnull(b.wgwj,0) as wgwj,isnull(b.sdf,0) as sdf,isnull(b.qtdk,0) as qtdk,isnull(OverTime,0) as OverTime,isnull(HourTime,0) as HourTime,isnull(Otherje,0) as Otherje,"
                    + " isnull(sbcdcs,0) as sbcdcs,isnull(xbztcs,0) as xbztcs,isnull(sbwkqcs,0) as sbwkqcs,isnull(xbwkqcs,0) as xbwkqcs,"
                    + " isnull(sjts,0) as sjts,isnull(bjts,0) as bjts,isnull(nxjts,0) as nxjts"
                    + " from [Web_UserInfo] a "
                    //+ " left join Web_LaborageDetailed b on a.ygbh=b.ygbh and b.rqtime='2018-07' "
                    + " left join [Web_LaborageDetailed] b on a.ygbh=b.ygbh and b.rqtime='" + month + "' "
                    + " left join [Web_Evaluation] d on b.ygbh=d.ygbh and b.djbh=d.djbh "
                    + " left join [Web_OverTime] e on b.ygbh=e.ygbh and b.djbh=e.djbh "
                    + " left join [Web_Department] c on a.bmid=c.did"
                    + " where a.beactive='是' and a.state not in('离职','停薪留职') ";
                if (Did != 1 && Did != 0)
                {
                    sql = sql + " and c.Did='" + Did + "'";
                }
                sql = sql + " order by a.bmid,a.ygbh";

                shelper = new MsSqlHelper();
                shelper.Conn.Open();
                MsSqlDao dao = new MsSqlDao();
                DataTable dt = dao.FindAll(shelper.Conn, sql);

                NPOIExportExclHelper excelHelper = new NPOIExportExclHelper();
                //MemoryStream ms = excelHelper.RenderDataTableToExcel(dt, title, "工资数据", title.Length) as MemoryStream;
                MemoryStream ms = excelHelper.RenderDataTableToExcel(dt, title, "工资数据", 7) as MemoryStream;
                Response.Clear();
                Response.Buffer = true;
                Response.ContentType = "application/vnd.ms-excel";
                //Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

                string fileName = HttpUtility.UrlEncode(string.Format("{1}_{0:yyyy-MM-dd_HH_mm}.xls", System.DateTime.Now, DepartmentName));

                Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}", fileName));
                Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8");
                Response.Charset = "utf-8";
                Response.BinaryWrite(ms.ToArray());
                ms.Close();
                ms.Dispose();
            }

        }
        catch (Exception ex)
        {
            CS.WriteLog(ex.ToString(), true);
            ListText.Text = "ERROR :" + ex.Message;
        }
    }
    
    /// <summary>
    /// 导出工资表
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void Button5_Click(object sender, EventArgs e)
    {
        try
        {
            ListText.Text = null;

            GridView1.DataSource = null;
            GridView1.DataBind();
            int Did = int.Parse(DropDownList1.SelectedValue.ToString());
            string month = DropDownList2.SelectedValue.ToString();
            string DepartmentName = DropDownList1.SelectedItem.Text.ToString();
            if (month == "0")
            {
                ListText.Text = "【<font color=red>请选择月份</font>】";
            }
            else if (Did == 0)
            {
                ListText.Text = "【<font color=red>请选择部门</font>】";
            }
            else
            {
                object[,] title = { { "序号", "月份", "员工编号", "姓名", "入司时间", "部门", "身份证号码",
                                      "基本工资", "岗位补贴", "绩效工资", "工龄工资", "加班工资", "交通补贴", "电话费", "其他工资","其他应扣", "提成工资", "应发合计", 
                                      "考勤考核", "违规违纪", "保险费", "公积金","其它代扣","应扣合计","应扣所得税","税后工资","水电费","培训费" } };
                string sql = "select rtrim(b.rqtime) as rqtime,isnull(b.ygbh,a.ygbh) ygbh,rtrim(a.xingming) as xingming,"
                            +" rtrim(a.rsjbqyshj) as rsjbqyshj,"
                            +" rtrim(c.DepartmentName) as DepartmentName,rtrim(idcard) as idcard,"

                            +" isnull(b.jbgz,0) as jbgz,isnull(b.gwbt,0) gwbt,"
                            +" isnull(b.sfjxgz,0) as sfjxgz,isnull(b.glgz,0) as glgz,"
                            +" isnull(b.jiabgz,0) as jiabgz,isnull(b.jtbt,0) as jtbt,"
                            + " isnull(b.dhf,0) as dhf,isnull(b.qtgz,0) as qtgz,isnull(b.dkhj,0) as dkhj,"
                            +" isnull(b.yygz,0) as yygz,isnull(b.yfhj,0) as yfhj,"

                            +" isnull(b.kqkh,0) as kqkh,isnull(b.wgwj,0) as wgwj,"
                            +" isnull(b.bxf,0) as bxf,isnull(b.gjj,0) as gjj,"
                            +" isnull(b.qtdk,0) as qtdk,isnull(b.ykgz,0) as ykgz,"
                            +" isnull(b.taxje,0) as taxje,"
					        +" isnull(b.shje,0) as shje,"
                            +" isnull(b.sdf,0) as sdf,isnull(b.pxf,0) as pxf"
                            +" from [Web_UserInfo] a "
                            //+"  --left join Web_LaborageDetailed b on a.ygbh=b.ygbh and b.rqtime='2018-07' "
                            +" left join [Web_LaborageDetailed] b on a.ygbh=b.ygbh and b.rqtime='" + month + "' "                
                            +" left join [Web_Department] c on a.bmid=c.did"
                            +" where a.beactive='是' and a.state not in('离职','停薪留职') "
                            +" and a.rsjbqyshj<=isnull(b.rqtime,'1900-01-01') "
                            +" and isnull(b.rqtime,'')<>''";
                if (Did != 1 && Did != 0)
                {
                    sql = sql + " and c.Did='" + Did + "'";
                }
                sql = sql + " order by a.bmid,a.ygbh";

                shelper = new MsSqlHelper();
                shelper.Conn.Open();
                MsSqlDao dao = new MsSqlDao();
                DataTable dt = dao.FindAll(shelper.Conn, sql);

                NPOIExportExclHelper excelHelper = new NPOIExportExclHelper();
                MemoryStream ms = excelHelper.RenderDataTableToExcel(dt, title, "工资数据", title.Length) as MemoryStream;
                //MemoryStream ms = excelHelper.RenderDataTableToExcel(dt, title, "工资数据",7) as MemoryStream;

                Response.Clear();
                Response.Buffer = true;
                Response.ContentType = "application/vnd.ms-excel";
                //Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

                string fileName = HttpUtility.UrlEncode(string.Format("{1}_{0:yyyy-MM-dd_HH_mm}.xls", System.DateTime.Now, DepartmentName));

                Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}", fileName));
                Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8");
                Response.Charset = "utf-8";
                Response.BinaryWrite(ms.ToArray());
                ms.Close();
                ms.Dispose();
            }
        }
        catch (Exception ex)
        {
            CS.WriteLog(ex.ToString(), true);
            ListText.Text = "ERROR :" + ex.Message;
        }
    }
    /// <summary>
    /// 重算工资数据
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void Button4_Click(object sender, EventArgs e)
    {
        ListText.Text = null;
        GridView1.DataSource = null;
        GridView1.DataBind();
        ListText.Text = CS.Redone();        
    }
    protected void Button7_Click(object sender, EventArgs e)
    {
        string month = DropDownList2.SelectedValue.ToString();
        if (month == "0")
        {
            ListText.Text = "【<font color=red>请选择月份</font>】";
        }
        else
        {
            ListText.Text = CS.NewLaborage(month);
        }
    }
    protected void Button8_Click(object sender, EventArgs e)
    {
        string month = DropDownList2.SelectedValue.ToString();
        if (month == "0")
        {
            ListText.Text = "【<font color=red>请选择月份</font>】";
        }
        else
        {
            ListText.Text = CS.QuoteLaborage(month);
        }
    }

    protected void Button9_Click(object sender, EventArgs e)
    {        
            ListText.Text =CS.InsertLaborage();
    }

    protected void Button10_Click(object sender, EventArgs e)
    {
        ListText.Text = CS.InsertLaborage();
    }
    /// <summary>
    /// 导出人员信息模板
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void Button11_Click(object sender, EventArgs e)
    {
        try
        {
            ListText.Text = null;
            GridView1.DataSource = null;
            GridView1.DataBind();
            int Did = int.Parse(DropDownList1.SelectedValue.ToString());
            string DepartmentName = DropDownList1.SelectedItem.Text.ToString();
                object[,] title = { { "序号", "员工编号","人事编号", "姓名", "部门","性别","民族","出生日期", "身份证号码", "开户银行", "银行账号", "电话", "保险类型", "保险号码", "学历", "入司时间"} };
                string sql = "select a.ygbh ygbh,rtrim(rsdabh) rsdabh,rtrim(a.xingming) as xingming,rtrim(c.DepartmentName) as DepartmentName,"
                           + "rtrim(sexid) as sexid,rtrim(rsmz) as rsmz,convert(varchar(10),csrq,120) as csrq,rtrim(idcard) as idcard, "
                           + "rtrim(khyh) as khyh,rtrim(yhzhh) as yhzhh,rtrim(telephoneno) as telphone,bxlx,shbx,rtrim(rszgxl) as rszgxl,rtrim(a.rsjbqyshj) as rsjbqyshj"
                    + " from [Web_UserInfo] a "
                    + " left join [Web_Department] c on a.bmid=c.did"
                    + " where a.beactive='是' and a.state not in('离职','停薪留职') ";
                     if (Did != 1 && Did != 0)
                {
                    sql = sql + " and c.Did='" + Did + "'";
                }
                    sql+= " order by a.bmid,a.ygbh";

                shelper = new MsSqlHelper();
                shelper.Conn.Open();
                MsSqlDao dao = new MsSqlDao();
                DataTable dt = dao.FindAll(shelper.Conn, sql);

                NPOIExportExclHelper excelHelper = new NPOIExportExclHelper();

                MemoryStream ms = excelHelper.RenderDataTableToExcel(dt, title, "人事信息数据",title.Length) as MemoryStream;
                Response.Clear();
                Response.Buffer = true;
                Response.ContentType = "application/vnd.ms-excel";
                //Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

                string fileName = HttpUtility.UrlEncode(string.Format("{1}_{0:yyyy-MM-dd_HH_mm}.xls", System.DateTime.Now, "人事信息数据"));

                Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}", fileName));
                Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8");
                Response.Charset = "utf-8";
                Response.BinaryWrite(ms.ToArray());
                ms.Close();
                ms.Dispose();
        }
        catch (Exception ex)
        {
            CS.WriteLog(ex.ToString(), true);
            ListText.Text = "ERROR :" + ex.Message+ex.ToString();
        }
    }
    protected void Button12_Click(object sender, EventArgs e)
    {
        try
        {
            ListText.Text = null;
            GridView1.DataSource = null;
            GridView1.DataBind();
            string type = Path.GetExtension(FileUpload1.FileName);
            if (!Directory.Exists(Server.MapPath("/Down/")))
                Directory.CreateDirectory(Server.MapPath("/Down/"));
            if (FileUpload2.FileName == "")
            {
                ListText.Text = "【<font color=red>请选择Excel文件</font>】";
            }
            else
            {
                string filePath = "Down/" + FileUpload2.FileName;
                if (File.Exists(Server.MapPath(filePath)))
                {
                    File.Delete(Server.MapPath(filePath));
                }
                FileUpload2.SaveAs(Server.MapPath(filePath));
                GridDataExcelSource(Server.MapPath(filePath));
                tempfilename = FileUpload2.FileName;
            }
        }
        catch (Exception ex)
        {
            CS.WriteLog(ex.ToString(), true);
            ListText.Text = "ERROR :" + ex.Message + ex.ToString();
        }
    }
    protected void Button13_Click(object sender, EventArgs e)
    {
        try
        {
            ListText.Text = null;
            GridView1.DataSource = null;
            GridView1.DataBind();
            //Response.Write("【" + tempfilename + "】<br>");
            if (tempfilename == "")
            {
                ListText.Text = "【<font color=red>请选择Excel文件</font>】";
            }
            else
            {
                if (!Directory.Exists(Server.MapPath("/Down/")))
                    Directory.CreateDirectory(Server.MapPath("/Down/"));
                string filePath = "Down/" + tempfilename;
                int i = 0;
                NPOIHelper Phelper = new NPOIHelper();
                DataTable dt = Phelper.ExcelToTable(Server.MapPath(filePath));

                if (dt.Equals(null)) Response.Write("dt = null");

                foreach (DataRow item in dt.Rows)
                {
                    //将Excel中的内容存入缓存中，一条条插入mysql数据库中
                    if (item.IsNull(0)) continue;
                    string dmsql = "delete from [ExcelDao_Userinfo] where ygbh=@p0";
                    string msql = "insert into [ExcelDao_Userinfo] ( ygbh, rsdabh, xingming, DepartmentName, sexid, rsmz, csrq, idcard, khyh, yhzhh, telphone, bxlx, shbx, rszgxl, rsjbqyshj)values(@p0,@p1,@p2,@p3,@p4,@p5,@p6,@p7,@p8,@p9,@p10,@p11,@p12,@p13,@p14)";
                    shelper = new MsSqlHelper();
                    shelper.Conn.Open();
                    MsSqlDao dao1 = new MsSqlDao();
                    dao1.InsertUserInfo(shelper.Conn, dmsql, item, i);
                    i = dao1.InsertUserInfo(shelper.Conn, msql, item, i);
                    shelper.Conn.Close();
                }
                if (i > 1)
                {
                    ListText.Text = "【<font color=green>导入成功</font>】";
                }
                else
                {
                    ListText.Text = "【<font color=red>导入失败</font>】";
                }
                //ehelper.Conn.Close();

                if (File.Exists(Server.MapPath(filePath)))
                {
                    File.Delete(Server.MapPath(filePath));
                }
                string sql = "update a set "+"a.rsdabh=case when b.rsdabh='' or b.rsdabh='0' then a.rsdabh else b.rsdabh end,"
                            +" a.sexid=case when b.sexid='' or b.sexid='0' then a.sexid else b.sexid end, "
                            +" a.rsmz=case when b.rsmz='' or b.rsmz='0' then a.rsmz else b.rsmz end, "
                            +" a.csrq=case when b.csrq='' or b.csrq='0' or ISDATE(b.csrq)=0 then a.csrq else b.csrq end, "
                            +" a.idcard=case when b.idcard='' or b.idcard='0' then a.idcard else b.idcard end, "
                            +" a.khyh=case when b.khyh='' or b.khyh='0' then a.khyh else b.khyh end, "
                            +" a.yhzhh=case when b.yhzhh='' or b.yhzhh='0' then a.yhzhh else b.yhzhh end, "
                            +" a.telephoneno=case when b.telphone='' or b.telphone='0' then a.telephoneno else b.telphone end, "
                            +" a.bxlx=case when b.bxlx='' or b.bxlx='0' then a.bxlx else b.bxlx end, "
                            +" a.shbx=case when b.shbx='' or b.shbx='0' then a.shbx else b.shbx end, " 
                            +" a.rszgxl=case when b.rszgxl='' or b.rszgxl='0' then a.rszgxl else b.rszgxl end, "
                            +" a.rsjbqyshj=case when b.rsjbqyshj='' or b.rsjbqyshj='0' then a.rsjbqyshj else b.rsjbqyshj end"
                            +" FROM [Web_UserInfo] a,ExcelDao_Userinfo b where a.ygbh=b.ygbh";
                shelper = new MsSqlHelper();
                shelper.Conn.Open();
                MsSqlDao dao = new MsSqlDao();
                dao.FindAll(shelper.Conn, sql);
            }
        }
        catch (Exception ex)
        {
            CS.WriteLog(ex.ToString(), true);
            ListText.Text = "ERROR :" + ex.Message+ex.ToString();
        }
    }
}
